package dao;

import java.sql.*;

import modle.Menu;

//  ` 通常用来说明其中的内容是数据库名、表名、字段名。所有的数据库都有类似的设置，不过Mysql用的是 ` 

public class MenuDao {

	//增
	public int MenuAdd(Connection con, Menu menu) throws Exception{
		String sql = "insert into `menu`(`Name`, `Id`, `Price`, `Description`, `Picture`) values(?,?,?,?,?)";
//		INSERT 语句中，前后列数不等会造成："mysql错误:Column count doesn't match value count at row 1"
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, menu.getName());
		pstmt.setString(2, menu.getId());
		pstmt.setString(3, menu.getPrice());
		pstmt.setString(4, menu.getDescription());
		pstmt.setString(5, menu.getPicture());
		return pstmt.executeUpdate();
	}
	
	//删
	public int MenuDel(Connection con, Menu menu) throws Exception{
		String sql = "delete from `menu` where Id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, menu.getId());
		return pstmt.executeUpdate();
	}
	
	//改
	public int MenuUpdate(Connection con, Menu menu, String id) throws Exception{
		String sql = "update `menu` set Name = ?, Id = ?, Price = ?, Description = ?, Picture = ? where Id = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, menu.getName());
		pstmt.setString(2, menu.getId());
		pstmt.setString(3, menu.getPrice());
		pstmt.setString(4, menu.getDescription());
		pstmt.setString(5, menu.getPicture());
		pstmt.setString(6, id);
		return pstmt.executeUpdate();
	}
	
	//查
	public Menu MenuSearch(Connection con, String Id) throws Exception{
		Menu resultMenu = null;
		
		String sql = "select * from `menu` where `Id` = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, Id);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			resultMenu = new Menu();							//new 一个空间，要不就会是空指针变量访问
			resultMenu.setName(rs.getString("Name"));
			resultMenu.setId(rs.getString("Id"));
			resultMenu.setPrice(rs.getString("Price"));
			resultMenu.setDescription(rs.getString("Description"));
			resultMenu.setPicture(rs.getString("Picture"));
		}
		
		return resultMenu;
	}
	
	public static Menu MenuSearch2(Connection con, int Rank) throws Exception{
		Menu resultMenu = null;
		
		String sql = "select * from `menu` where `Rank` = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, Rank);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			resultMenu = new Menu();							//new 一个空间，要不就会是空指针变量访问
			resultMenu.setName(rs.getString("Name"));
			resultMenu.setId(rs.getString("Id"));
			resultMenu.setPrice(rs.getString("Price"));
			resultMenu.setDescription(rs.getString("Description"));
			resultMenu.setPicture(rs.getString("Picture"));
			resultMenu.setRank(rs.getInt("Rank"));
		}
		
		return resultMenu;
	}
	
	public static Menu MenuSearch3(Connection con, String Name) throws Exception{
		Menu resultMenu = null;
		String sql = "select * from `menu` where `Name` = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, Name);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			resultMenu = new Menu();							//new 一个空间，要不就会是空指针变量访问
			resultMenu.setName(rs.getString("Name"));
			resultMenu.setId(rs.getString("Id"));
			resultMenu.setPrice(rs.getString("Price"));
			resultMenu.setDescription(rs.getString("Description"));
			resultMenu.setPicture(rs.getString("Picture"));
			resultMenu.setRank(rs.getInt("Rank"));
		}
		
		return resultMenu;
	}
}
